﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMS.DAL.DAHelpers;
using SMS.Entities;
using System.Data;

namespace SMS.DAL
{
    public class StockMasterDA : BaseDA<ProductStockWise>
    {
        public override ProductStockWise SaveObject(string userName, ProductStockWise tObject)
        {

            try
            {
                dbHelper.AddParameter("@Product_Stock_ID", tObject.Product_Stock_ID);
                dbHelper.AddParameter("@Product_ID", tObject.Product.Product_ID);
                dbHelper.AddParameter("@Stock_ID", tObject.Stock.Stock_ID);
                dbHelper.AddParameter("@Brand_ID", tObject.ProductBrand.Brand_ID);
                dbHelper.AddParameter("@Quantity", tObject.Quantity);
                dbHelper.AddParameter("@SoledQuantity", tObject.SoledQuantity);
                dbHelper.AddParameter("@BuyingPrice", tObject.BuyingPrice);
                dbHelper.AddParameter("@UOM", tObject.UOM);
                dbHelper.AddParameter("@SellingMargin", tObject.SellingMargin);
                dbHelper.AddParameter("@Discount", tObject.Discount);
                dbHelper.AddParameter("@IsSellingMarginPercentile", tObject.IsSellingMarginPercentile);
                dbHelper.AddParameter("@IsLive", tObject.IsLive);
                object objId = dbHelper.ExecuteScaler("USP_INSERT_UPDATE_PRODUCTSTOCKWISE", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Closed);
                if (Convert.ToInt32(objId) > 0)
                    tObject.Product_Stock_ID = Convert.ToInt32(objId);
                LogActivity(userName, string.Format("ProductStockWise Saved with ID {0}", tObject.Product_Stock_ID), tObject);
                return tObject;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, tObject);
                throw new Exception("Error occured while saving ProductStockWise");
            }
            #region Procedure
            /*ALTER PROCEDURE [dbo].[USP_INSERT_UPDATE_PRODUCTSTOCKWISE]
(@Product_Stock_ID int,
@Stock_ID int,
@Product_ID int,
@Brand_ID int,
@Quantity decimal(18,2),
@SoledQuantity decimal(18,2),
@BuyingPrice decimal(18,2),
@UOM varchar(50),
@SellingMargin decimal(18,2),
@Discount decimal(18,2),
@IsSellingMarginPercentile bit,
@IsLive bit)
as
if(@Product_Stock_ID=0)
begin
	INSERT INTO [Shop].[dbo].[ProductStockWise]
			   ([Stock_ID]
			   ,[Product_ID]
			   ,[Brand_ID]
			   ,[Quantity]
			   ,[SoledQuantity]
			   ,[BuyingPrice]
			   ,[UOM]
			   ,[SellingMargin]
			   ,[Discount]
			   ,[IsSellingMarginPercentile]
			   ,[IsLive])
		 VALUES
			   (@Stock_ID
			   ,@Product_ID
			   ,@Brand_ID
			   ,@Quantity
			   ,@SoledQuantity
			   ,@BuyingPrice
			   ,@UOM
			   ,@SellingMargin
			   ,@Discount
			   ,@IsSellingMarginPercentile
			   ,@IsLive)
	SELECT @@IDENTITY
end           
else           
begin
	UPDATE [Shop].[dbo].[ProductStockWise]
	   SET [Stock_ID] = @Stock_ID
		  ,[Product_ID] = @Product_ID
		  ,[Brand_ID] = @Brand_ID
		  ,[Quantity] = @Quantity
		  ,[SoledQuantity] = @Quantity
		  ,[BuyingPrice] = @BuyingPrice
		  ,[UOM] = UOM
		  ,[SellingMargin] = @SellingMargin
		  ,[Discount] = @Discount
		  ,[IsSellingMarginPercentile] = @IsSellingMarginPercentile
		  ,[IsLive] = @IsLive
	 WHERE Product_Stock_ID=@Product_Stock_ID
end*/
            #endregion
        }

        public override bool DeleteObject(string userName, ProductStockWise tObject)
        {
            throw new NotImplementedException();
        }

        public override List<ProductStockWise> GetObjectList(string userName, DateTime fromDate, DateTime toDate)
        {
            throw new NotImplementedException();
        }

        public List<ProductStockWise> GetProductsByStock(string userName, int StockID)
        {
            try
            {
                dbHelper.AddParameter("@Stock_ID", StockID);
                dbHelper.AddParameter("@Product_Stock_ID", 0);
                IDataReader dr = dbHelper.ExecuteReader("USP_SELECT_PRODUCT_STOCK_WISE", CommandType.StoredProcedure, ConnectionState.Closed);
                var list = new List<ProductStockWise>();
                while (dr.Read())
                {
                    list.Add(GetProductStockWiseObject(dr, false));
                }
                dr.Close();
                LogActivity(userName, string.Format("Get list of ProductStockWise of ID: [{0}] ", StockID), null);
                return list;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw new Exception("Errow while getting List of Product under stock");
            }
            #region Procedure
            /*
             ALTER PROCEDURE [dbo].[USP_SELECT_PRODUCT_STOCK_WISE]
(@Stock_ID int,
@Product_Stock_ID int)
as
IF (@Product_Stock_ID=0)
BEGIN
SELECT [Product_Stock_ID]
      ,[Stock_ID]
      ,[Product_ID]      
      ,[Quantity]      
      ,[IsLive]
      FROM ProductStockWise
      WHERE Stock_ID=@Stock_ID
END
ELSE
BEGIN
SELECT [Product_Stock_ID]
      ,[Stock_ID]
      ,P1.[Product_ID]
      ,[Quantity]
      ,P1.[IsLive]
      ,P.[Product_Name]
      ,B.[Brand_ID]
      ,B.[Brand_Name]      
      ,[SoledQuantity]
      ,[BuyingPrice]
      ,[UOM]
      ,[SellingMargin]
      ,[Discount]
      ,[IsSellingMarginPercentile]      
  FROM [Shop].[dbo].[ProductStockWise] P1 JOIN Product P 
		on P1.Product_ID=p.Product_ID JOIN Brand B 
		on P1.Brand_ID=B.Brand_ID
  WHERE Product_Stock_ID=@Product_Stock_ID
END
             */
            #endregion
        }

        public ProductStockWise GetCompleteProductStockWise(string userName, int product_stock_ID)
        {
            try
            {
                dbHelper.AddParameter("@Stock_ID", 0);
                dbHelper.AddParameter("@Product_Stock_ID", product_stock_ID);
                IDataReader dr = dbHelper.ExecuteReader("USP_SELECT_PRODUCT_STOCK_WISE", CommandType.StoredProcedure, ConnectionState.Closed);
                ProductStockWise obj = null;
                if (dr.Read())
                    obj = GetProductStockWiseObject(dr, true);
                LogActivity(userName, string.Format("Get CompleteProductStockWise of ID: [{0}] ", product_stock_ID), null);
                dr.Close();
                return obj;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw (new Exception(string.Format("Error while getting complete ProductStockWise ID [{0}]", product_stock_ID)));
            }

        }

        private ProductStockWise GetProductStockWiseObject(IDataReader dr, bool IsFull)
        {
            ProductStockWise obj = new ProductStockWise();
            int i = 0;
            obj.Product_Stock_ID = dr.GetInt32(i++);
            obj.Stock = new StockMaster { Stock_ID = dr.GetInt32(i++) };
            obj.Product = new Product { Product_ID = dr.GetInt32(i++) };
            obj.Quantity = dr.GetDecimal(i++);
            obj.IsLive = dr.GetBoolean(i++);
            if (IsFull)
            {
                obj.Product.Product_Name = dr.GetString(i++);
                obj.ProductBrand = new Brand { Brand_ID = dr.GetInt32(i++), Brand_Name = dr.GetString(i++) };
                obj.SoledQuantity = dr.GetDecimal(i++);
                obj.BuyingPrice = dr.GetDecimal(i++);
                obj.UOM = dr.GetString(i++);
                obj.SellingMargin = dr.GetDecimal(i++);
                obj.Discount = dr.GetDecimal(i++);
                obj.IsSellingMarginPercentile = dr.GetBoolean(i++);
            }
            return obj;
        }

        //private ProductStockWise

        public override List<ProductStockWise> SearchObject(string userName, string SearchText)
        {
            throw new NotImplementedException();
        }

        public List<ProductStockWise> GetAvailableProduct(string userName, int Product_ID)
        {
            System.Data.IDataReader reader = null;
            try
            {
                dbHelper.AddParameter("@Product_ID", Product_ID);
                reader = dbHelper.ExecuteReader("USP_CHECKPRODUCT_STOCK", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Open);
                List<ProductStockWise> results = new List<ProductStockWise>();
                while (reader.Read())
                {
                    results.Add(GetProductStockWiseObject(reader));
                }
                reader.Close();
                return results;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw (ex);
            }
        }

        public SaleDetail GenerateSaleDetail(string userName, int Product_Stock_ID)
        {
            System.Data.IDataReader reader = null;
            try
            {
                dbHelper.AddParameter("@Product_Stock_ID", Product_Stock_ID);
                reader = dbHelper.ExecuteReader("USP_GET_PRODUCTSTOCKWISE_BY_ID", System.Data.CommandType.StoredProcedure, System.Data.ConnectionState.Closed);
                SaleDetail saleDetail = new SaleDetail();
                ProductStockWise pObject = null;
                if (reader.Read())
                {
                    pObject = GetProductStockWiseObject(reader);
                }
                Product product = null;
                if (reader.NextResult() && reader.Read())
                {
                    product = ProductDA.GetProductObject(reader, false);
                    pObject.Product = product;
                }
                if (reader.NextResult() && reader.Read())
                {
                    Brand brand = new Brand();
                    brand.Brand_ID = reader.GetInt32(0);
                    brand.Brand_Name = reader.GetString(1);
                    pObject.ProductBrand = brand;
                }
                saleDetail.ProductStockWise = pObject;
                saleDetail.Quantity = 1;
                if (pObject.IsSellingMarginPercentile)
                    saleDetail.SellingUnitPrice = pObject.BuyingPrice + pObject.BuyingPrice * (pObject.SellingMargin * Convert.ToDecimal(.01));
                else
                    saleDetail.SellingUnitPrice = pObject.BuyingPrice + pObject.SellingMargin;
                saleDetail.Discount = pObject.Discount;
                reader.Close();
                LogActivity(userName, "Soled an item" + Product_Stock_ID, null);
                return saleDetail;
            }
            catch (Exception ex)
            {
                LogException(userName, ex, null);
                throw (ex);
            }

        }

        private ProductStockWise GetProductStockWiseObject(System.Data.IDataReader reader)
        {
            int i = 0;
            ProductStockWise product = new ProductStockWise
            {
                Product_Stock_ID = reader.GetInt32(i++),
                Stock = new StockMaster { Stock_ID = reader.GetInt32(i++) },
                Quantity = reader.GetDecimal(i++),
                SoledQuantity = reader.GetDecimal(i++),
                BuyingPrice = reader.GetDecimal(i++),
                UOM = reader.GetString(i++),
                SellingMargin = reader.GetDecimal(i++),
                Discount = reader.GetDecimal(i++),
                IsSellingMarginPercentile = reader.GetBoolean(i++)
            };
            return product;
        }
    }
}
